#include <soar.h>

#include "common.h"

int insert()
{/*{{{*/
    DBC_BIND bind;
    char buf[BUFFER_SIZE];
    int res;

    printf("%s\n", __func__);

    sprintf(buf,
            "insert into my_emp values(:0, to_date(:1,'YYYYMMDDHH24MISS'), :2, :3, :4, :5, :6)");
    printf("SQL: %s\n", buf);
    dbc_bind_int64(&bind, 0, 100);
    dbc_bind_varchar(&bind, 1, "20040829121212");
    dbc_bind_char(&bind, 2, " ", 20);
    dbc_bind_char(&bind, 3, "1", 1);
    dbc_bind_int(&bind, 4, 34);
    dbc_bind_varchar(&bind, 5, "云南");
    dbc_bind_varchar(&bind, 6, "岳阳楼记－范仲淹");
    res=oraproc_exec(buf, &bind, 7);
    if(res != ORA_OK)
    {
        printf("%s: fail, sqlcode:%d\n", __func__, res); 
        return(-1);
    }
    printf("Succeed\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int delete()
{/*{{{*/
    DBC_BIND bind;
    char buf[BUFFER_SIZE];
    int res;

    printf("%s\n", __func__);

    sprintf(buf,
            "delete from my_emp where empno=:0");
    printf("SQL: %s\n", buf);
    dbc_bind_int64(&bind, 0, 100);
    res=oraproc_exec(buf, &bind, 1);
    if(res != ORA_OK)
    {
        printf("%s: fail, sqlcode:%d\n", __func__, res); 
        return(-1);
    }
    printf("Succeed\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int update()
{/*{{{*/
    DBC_BIND bind;
    char buf[BUFFER_SIZE];
    int64_t empno;
    int res;

    printf("%s\n", __func__);

    sprintf(buf, "update my_emp set upd_ts=:0,resume=:1 where empno=:2");
    printf("SQL: %s\n", buf);
    empno=random_int(1, 10);
    printf("Update empno(%lld)的upd_ts...\n", empno);
    dbc_bind_varchar(&bind, 0, "20000101000000");
    dbc_bind_varchar(&bind, 1, "岳阳楼记－范仲淹");
    dbc_bind_int64(&bind, 2, empno);
    res=oraproc_exec(buf, &bind, 3);
    if(res != ORA_OK && res != ORA_NO_DATA_FOUND)
    {
        printf("%s: oraproc_exec fail, sqlcode:%d\n",
            __func__, res); 
        return(-1);
    }
    if(res == ORA_NO_DATA_FOUND)
    {
        printf("No data found\n"); 
        return(-1);
    }
    printf("Succeed\n");
    printf("==================================================\n");

    return(0);
}/*}}}*/

int query1()
{/*{{{*/
    DBC_BIND bind;
    DBC_RSET rset;
    int request_row_num;
    struct my_emp my_emp;
    int row_idx;
    char buf[BUFFER_SIZE];
    int res;

    printf("%s\n", __func__);

    sprintf(buf,
            "select * from my_emp where name=:0 order by empno asc");
    dbc_bind_char(&bind, 0, "职员1", sizeof(my_emp.name)-1);
    request_row_num=5;
    res=oraproc_query(buf, &bind, 1, request_row_num, &rset);
    if(res != ORA_OK)
    {
        printf("%s: fail, sqlcode:%d\n", __func__, res);
        return(-1);
    }

    printf("SQL: %s\n", buf);
    printf("结果:\n");
    for(row_idx=0; row_idx<rset.row_num; row_idx++)
    {
        my_emp.empno=dbc_col_export_int64(&rset, row_idx, 0);
        dbc_col_export_char(&rset, row_idx, 1, my_emp.upd_ts);
        dbc_col_export_char(&rset, row_idx, 2, my_emp.name);
        dbc_col_export_char(&rset, row_idx, 3, my_emp.duty);
        my_emp.age=dbc_col_export_int(&rset, row_idx, 4);
        dbc_col_export_char(&rset, row_idx, 5, my_emp.address);
        my_emp.resume=dbc_col_take_char(&rset, row_idx, 6);
        printf("%lld|%s|%s|%s|%d|%s|%s\n",
                my_emp.empno, my_emp.upd_ts, my_emp.name,
                my_emp.duty, my_emp.age, my_emp.address,
                my_emp.resume);
        FREE(my_emp.resume);
    }
    if(rset.query_flag == QUERY_BOTTOM)
        printf("查询到底\n");
    else
        printf("查询中途，有下一页\n");
    printf("==================================================\n");
    dbc_query_free(&rset);

    return(0);
}/*}}}*/

int query2()
{/*{{{*/
    DBC_BIND bind;
    DBC_RSET rset;
    char buf[BUFFER_SIZE];
    int res;
    int count, sum;

    printf("%s\n", __func__);

    sprintf(buf,
            "select count(*),sum(age) from my_emp where empno>:0");
    dbc_bind_int64(&bind, 0, 1);
    res=oraproc_query(buf, &bind, 1, -1, &rset);
    if(res != ORA_OK)
    {
        printf("%s: oraproc_query fail, sqlcode:%d\n", __func__, res);
        return(-1);
    }

    count=dbc_col_export_int(&rset, 0, 0);
    sum=dbc_col_export_int(&rset, 0, 1);
    printf("SQL: %s\n", buf);
    printf("结果: %d %d\n", count, sum);
    printf("==================================================\n");
    dbc_query_free(&rset);

    return(0);
}/*}}}*/

int query3()
{/*{{{*/
    DBC_BIND bind;
    DBC_RSET rset;
    int row_idx;
    struct my_emp my_emp;
    int count;
    char buf[BUFFER_SIZE];
    int res;

    printf("%s\n", __func__);

    sprintf(buf, "select duty,count(*) from my_emp where upd_ts between to_date(:0,'YYYYMMDDHH24MISS') and to_date(:1,'YYYYMMDDHH24MISS') group by duty order by duty desc");
    //sprintf(buf, "select duty,count(*) from my_emp where upd_ts between :0 and :1 group by duty order by duty desc");
    dbc_bind_varchar(&bind, 0, "20000101000000");
    dbc_bind_varchar(&bind, 1, "20100101000000");
    res=oraproc_query(buf, &bind, 2, -1, &rset);
    if(res != ORA_OK)
    {
        printf("%s: dbc_query fail, sqlcode:%d\n", __func__, res);
        return(-1);
    }

    printf("SQL: %s\n", buf);
    printf("结果:\n");
    for(row_idx=0; row_idx<rset.row_num; row_idx++)
    {
        dbc_col_export_char(&rset, row_idx, 0, my_emp.duty);
        count=dbc_col_export_int(&rset, row_idx, 1);
        printf("%s|%d\n", my_emp.duty, count);
    }
    printf("==================================================\n");
    dbc_query_free(&rset);

    return(0);
}/*}}}*/

int query4()
{/*{{{*/
    DBC_BIND bind;
    DBC_RSET rset;
    struct my_emp my_emp;
    struct my_salary my_salary;
    int row_idx;
    char buf[BUFFER_SIZE];
    int res;

    printf("%s\n", __func__);

    sprintf(buf,
            "select a.empno,a.name,b.salary "
            "from my_emp a, my_salary b "
            "where a.empno=b.empno and a.duty=:0 "
            "order by a.empno asc");
    dbc_bind_char(&bind, 0, "3", 1);
    res=oraproc_query(buf, &bind, 1, -1, &rset);
    if(res != ORA_OK)
    {
        printf("%s: fail, sqlcode:%d\n", __func__, res);
        return(-1);
    }

    printf("SQL: %s\n", buf);
    printf("结果:\n");
    for(row_idx=0; row_idx<rset.row_num; row_idx++)
    {
        my_emp.empno=dbc_col_export_int64(&rset, row_idx, 0);
        dbc_col_export_char(&rset, row_idx, 1, my_emp.name);
        my_salary.salary=dbc_col_export_double(&rset, row_idx, 2);
        printf("%lld|%s|%.2lf\n",
                my_emp.empno, my_emp.name, my_salary.salary);
    }
    printf("==================================================\n");
    dbc_query_free(&rset);

    return(0);
}/*}}}*/

void func()
{/*{{{*/
    int res;
    //int i;

    if((res=oraproc_connect("dbuser", "dbuser", NULL)) != ORA_OK)
    {
        printf("func: connect fail, sqlcode:%d\n", res); 
        return;
    }

    //for(i=0; i<10000; i++)
    //{
    oraproc_begin();

    if(
            insert()
            || delete()
            || update()
            || query1()
            || query2()
            || query3()
            || query4()
            )
    {
        oraproc_rollback();
        printf("回滚\n");
    }
    else
    {
        oraproc_commit();
        printf("提交\n");
    }
    //}

    oraproc_disconnect();

    return;
}/*}}}*/

int main(int argc, char *argv[])
{/*{{{*/
    oraproc_init();

    func();

    oraproc_clean();

    exit(0);
}/*}}}*/
